package kr.or.kead.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.Depart;
import kr.or.kead.domain.Professor;
import kr.or.kead.utils.MySqlCon;

public class DaoProfessor implements DaoTable {

	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		
		Professor professor = (Professor) obj;
		String sql = "insert into professor (code, name, departcode, course, email, password) values(?,?,?,?,?,?)";
		PreparedStatement pstmt = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, professor.getCode());
			pstmt.setString(2, professor.getName());
			pstmt.setInt(3, professor.getDepartCode());
			pstmt.setString(4, professor.getCourse());
			pstmt.setString(5, professor.getEmail());
			pstmt.setString(6, professor.getEmail());
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
		
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		
		Professor professor = (Professor) obj;
		
		String sql = "update professor set code=?, name=?, departCode=?, course=?, email=? where code = ? ";
				
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, professor.getCode());
			pstmt.setString(2, professor.getName());
			pstmt.setInt(3, professor.getDepartCode());
			pstmt.setString(4, professor.getCourse());
			pstmt.setString(5, professor.getEmail());
			pstmt.setInt(6, professor.getCode());
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
			
		String sql = "delete from professor where code = ?";
						
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		
		ArrayList<Object> professor = new ArrayList<>();
		
		String sql = "select code,name,departcode,course,email from professor";
				
		PreparedStatement pstmt = null;
		try {
			 pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next())
			{
				professor.add(new Professor(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5)));
			
			}
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
	

		return professor;
	}

	@Override
	public Object selectTable(int stdId) {
		Connection con = MySqlCon.getConn();
		
		
		String sql = "select code,name,departcode,course,email from professor where code = ?";
		
		PreparedStatement pstmt = null;
		try {
				pstmt = con.prepareStatement(sql);		
				pstmt.setInt(1, stdId);
				
				
				ResultSet rs = pstmt.executeQuery();
				
				if(rs.next())
				{
					return new Professor(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));
				}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
						
		return null;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
		
		String sql = "select code,name from professor";
		ArrayList<String> lists = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {			
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while (rs.next()){
				lists.add(rs.getInt(1)+":"+rs.getString(2));
			}			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return lists;
	}
	
	public int selectMaxCode(){
		Connection con = MySqlCon.getConn();
		String sql = "select max(code) from professor";
		
		PreparedStatement pstmt = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
						
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		
		return -1;
		
	}
	
	public Object selectCodeNameByid(int code) {
		Connection con = MySqlCon.getConn();
		
		String sql = "select code,name from professor where code = ?";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {			
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, code);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				return(rs.getInt(1)+":"+rs.getString(2));
			}			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return null;
		
	}
	public String selectPassWordByEmail(String email){
		Connection con = MySqlCon.getConn();
		String sql = "select password from professor where email = ?";
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return null;
	}

	@Override
	public Object selectTableByEmail(String email) {
		Connection con = MySqlCon.getConn();
		String sql = "select code, name, departcode, course, email, password from professor where email = ?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return new Professor(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return null;
	}

	@Override
	public int updatePasswd(String pass, String email) {
		Connection con = MySqlCon.getConn();
		Professor professor = new Professor();		
		String sql = "update professor set password=? where email = ? ";
				
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, pass);
			pstmt.setString(2, email);
			System.out.println(pstmt);			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
	}
	public ArrayList<String> selectNameByEmail(String email){
		Connection con = MySqlCon.getConn();
		String sql = "select name from professor where email = ?";
		ArrayList<String> lists = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			while(rs.next()){
				lists.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return lists;
	}
}


